
drop database isales

create database isales
go
--A. CREATE TABLE

create table tbl_group_customer
(
	grp_cus_id integer identity primary key,
	grp_cus_name nvarchar(50)
)
go
create table tbl_customers
(
	cus_id integer identity primary key,
	code varchar(50) unique not null,
	cus_code varchar(50) unique not null,
	cus_name nvarchar(50),
	cus_account nvarchar(50),
	bank nvarchar(50),
	email char(50) ,
	addr nvarchar(50),
	phone char(10),
	cel_phone char(12),
	fax char(20),
	min_debt decimal(18,5),
	emp_id integer not null,
	contact_name nvarchar(50),
	type_ex_price integer,
	grp_cus_id integer not null, -- fk group customer
	visible integer
)
go

create table tbl_parts
(
	parts_id integer identity primary key,
	parts_name nvarchar(20)
)
go

create table tbl_Account
(
	acc_id integer identity primary key,
	user_code varchar(20) unique,
	user_names varchar(20) unique,
	user_pss varchar(20),
	parts_id integer not null -- fk parts
)
go

create table tbl_employee
(
	emp_id integer identity primary key,
	emp_name nvarchar(50)not null,
	emp_code varchar(50),
	emp_pwd varchar(20),
	email varchar (50),
	addr nvarchar(50),
	phone char (10),
	cell_phone char(12),
	full_access decimal(1,0),
	emp_role nvarchar(Max),
	visible integer	,
	acc_id integer not null -- fk account
)
go

create table tbl_kindof_product
(
	kindof_pro_id integer identity primary key,
	kindof_pro_name nvarchar(50)
)
go

create table tbl_group_product
(
	grp_pro_id integer identity primary key,
	grp_pro_name nvarchar(50),
	kindof_pro_id integer, -- fk kind of product
)
go
create table tbl_product
(
	pro_id integer identity primary key,
	pro_name nvarchar(50),
	barcode varchar (20), --? unique
	pro_code varchar(50) unique ,-- ma san pham,
	pro_serial varchar(50) unique, -- ma so san pham
	min_amount decimal (18,3),
	im_price decimal(18,3),
	ex1_price decimal(18,3),
	ex2_price decimal(18,3),
	ex3_price decimal(18,3),
	ex4_price decimal(18,3),
	visible int,
	grp_pro_id int, -- nhom san pham -
	store_id int ,--pk store
	unit_id int --pk unit	
)
go

create table tbl_group_provider
(
	grp_prov_id integer identity primary key,
	grp_prov_name nvarchar(50)
)
go

create table tbl_provider
(
	prov_id integer identity primary key,
	prov_name nvarchar(50),
	email varchar(50),
	addr nvarchar(50),
	phone char(10),
	cell_phone char(12),
	fax char(20),
	post_name nvarchar(50),-- chức vụ
	visible int ,
	grp_prov_id int -- fk group khach hang
)
go

create table tbl_order
(
order_id integer identity primary key,
order_num varchar(50) ,
date_reg smalldatetime,
type_order int ,-- loai chung tu xuat / nhap
order_val decimal(18,5),
visiable int, -- trang thai chung tu
cus_id integer --pk customer,

)
go

create table tbl_order_detail
(
detail_id integer identity primary key,
amount decimal(18,3),
order_id integer , --pk ma chung tu
pro_id integer --pl ma so san pham
)
go

create table tbl_store
(
store_id integer identity primary key,
store_name nvarchar(50) not null,
)
go

create table tbl_unit
(
unit_id integer identity primary key,
unit_name nvarchar(50) not null
)
go


--B. AlTER TABLE

alter table tbl_Account
add
	Constraint FK_Acc_Parts Foreign key(parts_id) references tbl_parts(parts_id)
go

alter table tbl_employee
add
	Constraint FK_Emp_Acc Foreign key(acc_id) references tbl_Account(acc_id)
go

alter table tbl_customers
add
	Constraint FK_Cus_GrpCus Foreign key(grp_cus_id) references tbl_group_customer(grp_cus_id),
	Constraint FK_Cus_Emp Foreign key(emp_id) references tbl_employee(emp_id)
go

alter table tbl_group_product
add
	Constraint FK_GrpPro_Kindof Foreign key(kindof_pro_id) references tbl_kindof_product(kindof_pro_id)	
go

alter table tbl_product
add
	Constraint FK_Pro_GrpPro Foreign key(grp_pro_id) references tbl_group_product(grp_pro_id),
	Constraint FK_Pro_Store Foreign key(store_id) references tbl_store(store_id),	
	Constraint FK_Pro_Unit Foreign key(unit_id) references tbl_unit(unit_id)	
go


alter table tbl_provider
add
	Constraint FK_Prov_GrpProv Foreign key(grp_prov_id) references tbl_group_provider(grp_prov_id)	
go

alter table tbl_order
add
	Constraint FK_Order_Cus Foreign key(cus_id) references tbl_customers(cus_id)	
go

alter table tbl_order_detail
add
	Constraint FK_OrDet_Order Foreign key(order_id) references tbl_order(order_id),	
	Constraint FK_OrDet_Pro Foreign key(pro_id) references tbl_product(pro_id)
go


--C. INSERT TABLE

insert into tbl_unit (unit_name ) values(N'Thùng')
insert into tbl_unit (unit_name ) values(N'Hộp')
insert into tbl_unit (unit_name ) values(N'Kg')

insert into tbl_store ( store_name  ) values( N'Kho A')
insert into tbl_store ( store_name  ) values( N'Kho A1')
insert into tbl_store ( store_name ) values( N'Kho A2')
insert into tbl_store ( store_name ) values( N'Kho B')

insert into tbl_parts (parts_name )values(N'Giám đốc')
insert into tbl_parts (parts_name )values(N'Quản lý')
insert into tbl_parts (parts_name )values(N'Nhân viên')

insert into tbl_Account (user_code , user_names , user_pss , parts_id )
values ('GD01', 'admin', '123',1)
insert into tbl_Account (user_code , user_names , user_pss , parts_id )
values ('NV01', 'NV01', '123',3)

insert into tbl_employee (emp_code , emp_name ,email ,addr ,phone ,cell_phone ,full_access ,emp_role ,visible, acc_id )
    values('AD000001', N'Lâm Thanh Hà', 'tha@gmail.com', N'2 Kinh Dương vương', '0823455544', '0165333222',1,N'Toàn quyền',1,1)
insert into tbl_employee (emp_code , emp_name ,email ,addr ,phone ,cell_phone ,full_access ,emp_role ,visible, acc_id )
    values('NV000001', N'Lâm Văn Kha', 'htq@gmail.com', N'12 Kinh Dương vương', '0823455509', '0165333967',1,N'Theo dõi đơn hàng',1,2)
    	
--insert into tbl_customer (code,cus_name ,cus_code ,cus_account ,bank ,email , addr , cel_phone, phone ,fax , contact_name , min_debt , type_ex_price ,group_id ,emp_id )
--	values('KH000001',N'Nguyễn Văn A', '01010101' ,'0110001001',N'DONGA Bank','abc@gmail.com',N'124/Lương Văn Cang','0907362433','0834567890','',N'Phan Văn B',5,2,3,1)
--insert into tbl_customer (code,cus_name ,cus_code ,cus_account ,bank ,email , addr , cel_phone, phone ,fax , contact_name , min_debt , type_ex_price ,group_id ,emp_id )
--	values('KH000002',N'Nguyễn Văn B', '01010102' ,'0110001002',N'BIDV Bank','abf@gmail.com',N'124/Hùng Vương','0907362432','0834567891','',N'Trình Thiệu Phong',5,2,3,1)
	

--insert into tbl_product (pro_name ,pro_code ,pro_serial ,pro_group , barcode , cate_id , im_price, min_amount, store_id , ex1_price ,ex2_price ,ex3_price ,ex4_price ,unit_id,visible)
--	values(N'SP A', 'SP00001', '0000001', 1, '11110000', 1,3,3,1,3,5,10,18,1,1)
--insert into tbl_product (pro_name ,pro_code ,pro_serial ,pro_group , barcode , cate_id , im_price, min_amount, store_id , ex1_price ,ex2_price ,ex3_price ,ex4_price ,unit_id,visible)
--	values(N'SP B', 'SP00002', '0000002', 1, '11110001', 1,3,3,1,3,5,10,18,1,1)	

--insert into tbl_order (order_num ,order_val ,type_order ,date_reg ,cus_id ,visiable )
--	values('12345',5,1,'2014-02-01',1,1)
--insert into tbl_order (order_num ,order_val ,type_order ,date_reg ,cus_id ,visiable )
--	values('12346',5,2,'2014-02-01',1,2)

--insert into tbl_order_detail (order_id ,pro_id  ,amount )values (1,1,5)
--insert into tbl_order_detail (order_id ,pro_id  ,amount )values (2,2,5)

--insert into tbl_provider (prov_name ,email ,addr ,phone ,cell_phone ,fax ,post_name ,visible ,group_id )
--values(N'Công ty TNHH TH', 'th@gmail.com',N'7 Hồng Lĩnh', '083456789', '01235433976','',N'Giám đốc',1,4)
--insert into tbl_provider (prov_name ,email ,addr ,phone ,cell_phone ,fax ,post_name ,visible ,group_id )
--values(N'Công ty TNHH TT', 'tt@gmail.com',N'6 Bà Hôm, Q.6', '083456780', '01245433977','',N'Giám đốc',1,4)		

	